const ExcelJS = require('exceljs');
const { PassThrough } = require('stream');
const connection = require('../sql').rawPool;
const fs = require('fs');
/**
 * 通用 Excel 导出中间件（支持大数据量导出）
 * @param {Object} ctx - Koa 上下文
 * @param {string|Array|stream.Readable} dataSource - SQL 语句 | 数据数组 | 数据流
 * @param {Array} params - SQL 参数（若 dataSource 为 SQL）
 * @param {Object} options - 配置项
 * @param {Object} options.headerMap - 表头映射（key 为字段名，value 为标题）
 * @param {Array} options.headers - 表头字段名数组（优先使用 headerMap）
 * @param {string} options.fileName - 导出文件名（不含扩展名）
 * @param {string} options.sheetName - 工作表名
 * @param {mysql2.Connection} options.connection - MySQL 连接（可选，默认为 rawPool）
 */
async function exportExcelMiddleware(ctx, dataSource, params = [], options = {}) {
  const { headers = null, headerMap = null, fileName = 'export', sheetName = 'Sheet1' } = options;

  // 验证数据库连接
  if (!connection || typeof connection.query !== 'function') {
    ctx.status = 500;
    ctx.body = { code: 500, message: '缺少有效的数据库连接' };
    return;
  }

  // 验证表头
  if (!headers && !headerMap) {
    ctx.status = 400;
    ctx.body = { code: 400, message: '请传入有效的 headerMap 或 headers' };
    return;
  }

  const output = new PassThrough();
  const workbook = new ExcelJS.stream.xlsx.WorkbookWriter({
    stream: output,
    useStyles: true,
    useSharedStrings: true // 优化内存使用
  });
  const worksheet = workbook.addWorksheet(sheetName);

  // 设置响应头
  ctx.set({
    'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    'Content-Disposition': `attachment; filename*=UTF-8''${encodeURIComponent(fileName)}.xlsx`
  });
  ctx.body = output;

  try {
    let keys = [],
      headerRow;

    // 设置表头
    if (headerMap && typeof headerMap === 'object') {
      keys = Object.keys(headerMap);
      headerRow = worksheet.addRow(Object.values(headerMap));
    } else if (Array.isArray(headers)) {
      keys = headers;
      headerRow = worksheet.addRow(headers);
    } else {
      throw new Error('无效的表头配置');
    }

    // 🔽 加样式
    headerRow.eachCell((cell, colNumber) => {
      cell.font = { bold: true, size: 12 };
      cell.alignment = { vertical: 'middle', horizontal: 'center' };
      cell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFB8CCE4' } // 蓝色背景
      };
    });
    headerRow.commit();

    keys.forEach((key, index) => {
      const column = worksheet.getColumn(index + 1);
      column.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true };
      column.width = 30;
    });

    // 处理数据
    if (typeof dataSource === 'string') {
      // SQL 数据流
      const stream = connection.query(dataSource, [...params]).stream({
        highWaterMark: 16384 // 增加缓冲区大小以优化大数据量处理
      });

      stream.on('data', row => {
        const rowData = keys.map(k => row[k] ?? '');
        worksheet.addRow(rowData).commit();
      });

      stream.on('end', async () => {
        try {
          await workbook.commit();
          output.end();
        } catch (err) {
          console.error('工作簿关闭失败：', err);
          ctx.status = 500;
          ctx.body = { code: 500, message: '工作簿关闭失败', error: err.message };
        }
      });

      stream.on('error', err => {
        console.error('数据库流错误：', err);
        ctx.status = 500;
        ctx.body = { code: 500, message: '数据库流错误', error: err.message };
        output.end();
      });
    } else if (Array.isArray(dataSource)) {
      // 数据数组
      for (const row of dataSource) {
        const rowData = keys.map(k => row[k] ?? '');
        worksheet.addRow(rowData).commit();
      }
      await workbook.commit();
      output.end();
    } else if (dataSource && typeof dataSource.pipe === 'function') {
      // 自定义数据流
      dataSource.on('data', row => {
        const rowData = keys.map(k => row[k] ?? '');
        worksheet.addRow(rowData).commit();
      });

      dataSource.on('end', async () => {
        try {
          await workbook.commit();
          output.end();
        } catch (err) {
          console.error('工作簿关闭失败：', err);
          ctx.status = 500;
          ctx.body = { code: 500, message: '工作簿关闭失败', error: err.message };
        }
      });

      dataSource.on('error', err => {
        console.error('数据流错误：', err);
        ctx.status = 500;
        ctx.body = { code: 500, message: '数据流错误', error: err.message };
        output.end();
      });
    } else {
      throw new Error('不支持的数据类型：请传入 SQL 字符串、对象数组或可读流');
    }
  } catch (err) {
    console.error('导出失败：', err);
    ctx.status = 500;
    ctx.body = { code: 500, message: '导出失败', error: err.message };
    output.end();
  }
}

/**
 * 读取 Excel 文件并返回数据
 * @param {stream.Readable|string} fileStream - 文件流或文件路径
 * @param {Object} headerMap - 表头映射（key 为字段名，value 为标题）
 * @returns {Promise<Array>} - 解析后的行数据数组
 */
async function readExcelStream(file, headerMap) {
  const fileStream = fs.createReadStream(file.filepath);
  const workbook = new ExcelJS.Workbook();
  const rows = [];

  try {
    // 使用流式读取
    await workbook.xlsx.read(fileStream);

    const worksheet = workbook.getWorksheet(1);
    if (!worksheet) {
      throw new Error('工作表不存在');
    }

    // 获取表头
    const headerRow = worksheet.getRow(1);
    const reverseHeaderMap = Object.fromEntries(Object.entries(headerMap).map(([key, value]) => [value, key]));
    const fieldIndices = {};

    headerRow.eachCell((cell, colNumber) => {
      const headerText = cell.text.trim();
      if (reverseHeaderMap[headerText]) {
        fieldIndices[colNumber] = reverseHeaderMap[headerText];
      }
    });

    if (Object.keys(fieldIndices).length === 0) {
      throw new Error('表头不匹配或缺失');
    }

    // 读取数据行
    worksheet.eachRow({ includeEmpty: false }, (row, rowNumber) => {
      if (rowNumber === 1) return; // 跳过表头

      const rowData = {};
      row.eachCell((cell, colNumber) => {
        if (fieldIndices[colNumber]) {
          rowData[fieldIndices[colNumber]] = cell.text ? cell.text.trim() : '';
        }
      });

      if (Object.keys(rowData).length > 0) {
        rows.push(rowData);
      }
    });

    return rows;
  } catch (error) {
    console.error('读取 Excel 错误:', error.message);
    throw error;
  }
}
module.exports = {
  exportExcelMiddleware,
  readExcelStream
};
